package core.helpers;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import core.Pilot;

/**
 * Class provides the methods for working with database
 */
public class DBHelper
{
	protected static Pilot pilot;
	protected static ResultSet queryResult;
	private static Connection connection;
	private static Statement statement;

	/**
	 * Constructor
	 * @param p_pilot
	 */
	public DBHelper(Pilot p_pilot)
	{
		pilot = p_pilot;
	}

	/**
	 * Connects to DB
	 * @param p_dbHostName
	 * @param p_dbPort
	 * @param p_dbName
	 * @param p_dbUserName
	 * @param p_dbUserPassword
	 */
	public void connect(String p_dbHostName, String p_dbPort, String p_dbName, String p_dbUserName, String p_dbUserPassword)
	{
		try
		{
			pilot.log().info("Connect to DB [dbHostName=" + p_dbHostName + "; dbPort=" + p_dbPort + "; dbName=" + p_dbName + "; dbUserName=" + p_dbUserName
					+ "; dbUserPassword=" + p_dbUserPassword + "]");
			DriverManager.registerDriver(new com.mysql.jdbc.Driver());
			connection = DriverManager.getConnection("jdbc:mysql://" + p_dbHostName + ":" + p_dbPort + "/" + p_dbName + "?zeroDateTimeBehavior=convertToNull",
					p_dbUserName, p_dbUserPassword);
			statement = connection.createStatement();
			queryResult = null;
		}
		catch (Exception p_exception)
		{
			pilot.log().warn(
					"Cannot connect to DB [dbHostName=" + p_dbHostName + "; dbPort=" + p_dbPort + "; dbName=" + p_dbName + "; dbUserName=" + p_dbUserName
							+ "; dbUserPassword=" + p_dbUserPassword + "]\nError: " + p_exception);
		}
	}

	/**
	 * Disconnects from DB
	 */
	public void disconnect()
	{
		try
		{
			connection.close();
			pilot.log().info("Disconnect fom DB");
		}
		catch (NullPointerException p_exception)
		{

		}
		catch (Exception p_exception)
		{
			pilot.log().warn("Cannot disconnect from DB\nError: " + p_exception);
		}
	}

	/**
	 * Sends query: 'SELECT'
	 * @param p_query
	 *            String with query
	 * @return Query result
	 */
	public ResultSet querySelect(String p_query)
	{
		queryResult = null;

		try
		{
			pilot.log().info("Send query to DB [" + p_query + "]");
			queryResult = statement.executeQuery(p_query);
		}
		catch (Exception p_exception)
		{
			pilot.log().warn("Cannot send query to DB [" + p_query + "]\nError: " + p_exception);
		}

		if (queryResult == null)
		{
			pilot.log().warn("Query result is null [" + p_query + "]");
		}

		return queryResult;
	}

	/**
	 * Sends query: 'DROP TABLE', 'DROP DATABASE', 'INSERT INTO TABLE', 'UPDATE TABLE', 'DELETE FROM TABLE'
	 * @param p_query
	 */
	public void query(String p_query)
	{
		try
		{
			pilot.log().info("Send query to DB [" + p_query + "]");
			statement.executeUpdate(p_query);
		}
		catch (Exception p_exception)
		{
			pilot.log().warn("Cannot send query to DB [" + p_query + "]\nError: " + p_exception);
		}
	}

	/**
	 * Checks connection to DB
	 * @return Connetion status
	 */
	public boolean isConnected()
	{
		try
		{
			if ((connection != null && connection.isValid(0)))
			{
				pilot.log().info("Check connection status to DB => " + true);
				return true;
			}
			else
			{
				pilot.log().warn("Check connection status to DB => " + false);
				return false;
			}

		}
		catch (SQLException p_exception)
		{
			pilot.log().warn("Cannot check connection status to DB\nError: " + p_exception);
			return false;
		}

	}

	/**
	 * Gets count of rows in a query result
	 * @param p_queryResult
	 * @return Count of rows
	 */
	protected int getRowCount(ResultSet p_queryResult)
	{
		int rowCount = 0;

		try
		{
			if (p_queryResult.last())
			{
				rowCount = queryResult.getRow();
				p_queryResult.beforeFirst();
				pilot.log().info("Get count of rows in the query => " + rowCount);

				return rowCount;
			}
		}
		catch (SQLException p_exception)
		{
			pilot.log().warn("Cannot get count of rows\nError: " + p_exception);
		}

		return rowCount;
	}
}